﻿var t_arr = new Array(); //选择的视图数组，Array【表名】
var c_arr = new Array(); //选择的列字段2唯数组，Array【表名】【表名.字段名】
var c_arr_all = new Array(); //选择的列字段2唯全字段数组，Array【表名】【字段名】///

var on_Arr = new Array();
var where_Arr = new Array();
var order_Arr = new Array();

var viewAll = true;
//页面方法---begin
function Save() {
    var vm = $.trim($("#txt_ViewName").val());
    if (IsNullOrEmpty(vm)) {
        alert("视图名称不能为空！");
        return;
    }
    if (checkStringNum(vm)) {
        alert("视图名称要以英文字母开始！并且只能用英文、数字和\"_\".");
        return;
    }
    CreateDefinition();
    $("#hid_JoinType").val($('#viewShip input:radio:checked').val());
    $("#hid_TopNum").val($('#txt_Top').val());
    $.post("/SysTBView/SaveItem", { query: jv.GetDivJson($("#formArea"), 4), selectView: jv.GetDivJson($("#selectView")), selectArea: jv.GetDivJson($(".selectArea")), onArea: jv.GetDivJson($(".onArea"), 4), whereArea: jv.GetDivJson($(".whereArea"), 4), orderByArea: jv.GetDivJson($(".orderByArea"), 4) }, function (res) {
        if (res == "OK") {
            alert("操作成功！");
            back();
        } else {
            alert(res);
        }
    });
}
function back() {
    window.location.href = "/SysView/ViewList";
}
$(function () {
    var id = $("#hid_ID").val();
    $.ajax({
        type: "POST",
        async: false,  // 设置同步方式 
        url: "/SysTBView/SelectView/?id=" + id,
        success: function (data) {
            if (data != "") {
                $("#selectView").append(data);
            }
        }
    });
    $.ajax({
        type: "POST",
        async: false,  // 设置同步方式 
        url: "/SysTBView/SavedView/?id=" + id + "&index=0",
        success: function (TBdata) {
            //---------------ON-----------
            $.ajax({
                type: "POST",
                async: false,  // 设置同步方式 
                url: "/SysTBView/SavedView/?id=" + id + "&index=2",
                success: function (data) {
                    if (data != "[]" && data != "") {
                        for (var i = 0; i < eval(data).length; i++) {
                            var j = i + 1;
                            var left = eval(data)[i].LeftColumn;
                            var right = eval(data)[i].RightColumn;
                            var lf = left.substr(0, left.lastIndexOf("."));
                            var lb = left.substr(left.lastIndexOf(".") + 1);
                            var rf = right.substr(0, right.lastIndexOf("."));
                            var rb = right.substr(right.lastIndexOf(".") + 1);
                            //---------SELECT Area 
                            for (var tbi = 0; tbi < eval(TBdata).length; tbi++) {
                                var en = eval(TBdata)[tbi].EnName;
                                var bid = eval(TBdata)[tbi].BaseViewID;
                                if (en == lf && $("#div_" + bid).length == 0) {
                                    if ($("#" + bid).length > 0) {
                                        $("#" + bid).attr("checked", "checked");
                                        selectItem(bid, $("#" + bid)[0].className);
                                    }
                                }
                            }
                            for (var tbi = 0; tbi < eval(TBdata).length; tbi++) {
                                var en = eval(TBdata)[tbi].EnName;
                                var bid = eval(TBdata)[tbi].BaseViewID;
                                if (en == rf && $("#div_" + bid).length == 0) {
                                    if ($("#" + bid).length > 0) {
                                        $("#" + bid).attr("checked", "checked");
                                        selectItem(bid, $("#" + bid)[0].className);
                                    }
                                }
                            }
                            addOn();
                            $(".fdropLeftOn_" + j + " option[text='" + lf + "']").attr("selected", true);
                            DropChange($(".fdropLeftOn_" + j).val(), "dropLeftOn_" + j);
                            $("#dropLeftOn_" + j).val(left);
                            $(".fdropRightOn_" + j + " option[text='" + rf + "']").attr("selected", true);
                            DropChange($(".fdropRightOn_" + j).val(), "dropRightOn_" + j);
                            $("#dropRightOn_" + j).val(right);
                        }
                    } else if (TBdata != "") {
                        for (var i = 0; i < eval(TBdata).length; i++) {
                            var bid = eval(TBdata)[i].BaseViewID;
                            if ($("#" + bid).length > 0) {
                                $("#" + bid).attr("checked", "checked");
                                selectItem(bid, $("#" + bid)[0].className);
                            }
                        }
                    }
                }
            });
            //--------------where-----------
            $.ajax({
                type: "POST",
                async: false,  // 设置同步方式 
                url: "/SysTBView/SavedView/?id=" + id + "&index=3",
                success: function (data) {
                    if (data != "") {
                        for (var i = 0; i < eval(data).length; i++) {
                            var j = i + 1;
                            var wh = eval(data)[i].ViewColumn;
                            var lf = wh.substr(0, wh.lastIndexOf("."));
                            var lb = wh.substr(wh.lastIndexOf(".") + 1);
                            addWhere();
                            $(".fdropViewColumn_" + j + " option[text='" + lf + "']").attr("selected", true);
                            DropChange($(".fdropViewColumn_" + j).val(), "dropViewColumn_" + j);
                            $("#dropViewColumn_" + j).val(wh);
                            $("#wha_Action" + j).val(eval(data)[i].Action);
                            $("input[name='whe_ActionText_" + j + "']").val(eval(data)[i].ActionText);
                            $("#grp_GroupBy" + j).val(eval(data)[i].GroupBy);
                        }
                    }
                }
            });
            //--------------order by-----------
            $.ajax({
                type: "POST",
                async: false,  // 设置同步方式 
                url: "/SysTBView/SavedView/?id=" + id + "&index=4",
                success: function (data) {
                    if (data != "") {
                        for (var i = 0; i < eval(data).length; i++) {
                            var j = i + 1;
                            var vc = eval(data)[i].ViewColumn;
                            var lf = vc.substr(0, vc.lastIndexOf("."));
                            var lb = vc.substr(vc.lastIndexOf(".") + 1);
                            addOrderBy();
                            $(".fdropOrder_" + j + " option[text='" + lf + "']").attr("selected", true);
                            DropChange($(".fdropOrder_" + j).val(), "dropOrder_" + j);
                            $("#dropOrder_" + j).val(vc);
                            $("#oda_SortType" + j).val(eval(data)[i].SortType);
                            $("#sor_SortNum" + j).val(eval(data)[i].SortNum);
                        }
                    }
                }
            });
            CreateDefinition();
        }
    });
});
function InitDropList(val, id) {
    var p = val.substr(0, val.lastIndexOf("."));
    var v = val.substr(val.lastIndexOf(".") + 1);
    $("#" + id + "option[value='" + val + "']").attr("selected", true);
}
//页面方法---end

//配置方法---begin
//
function select_All() {
    var selector = $("#selectView input");
    if ($("#select_All").is(':checked')) {
        selector.attr("checked", "checked");
        if (selector.length > 3) {
            lockJoin();
        }
    }
    else {
        selector.removeAttr("checked");
        UnlockJoin();
    }
    selector.each(function (i, d) {
        var nm = d.className;
        if (jv.IsNull(nm)) return;
        selectItem(d.value, nm);
    });
}

function lockJoin() {
    $("#viewShip input").attr("disabled", true);
    $("#viewShip input[value='INNER JOIN']").attr("checked", "checked");
}
function UnlockJoin() {
    $("#viewShip input").attr("disabled", false);
}
function ArrIndexOf(arr, v) {
    for (var i = 0; i < arr.length; i++) {
        if (typeof (arr[i]) == "object") {
            return ArrIndexOf(arr[i], v);
        } else {
            if (arr[i].startWith(v) || arr[i].endWith(v))
                return true;
        }
    }
    return false;
}
function selectItem(id, en) {
    var item = $("#" + id);
    var arrNum = t_arr.length;
    if (item.is(':checked') && t_arr.indexOf(en) == -1) {
        t_arr[arrNum] = en;
        c_arr[arrNum] = en;
        if (!ArrIndexOf(c_arr_all, en + "."))
            c_arr_all.push(en);
        if (t_arr.length > 2)
            lockJoin();
        var divhtml = "<div class='view_tab view_tab_f' style='width: 44%;' id='div_" + id +
            "'><div class='table_Title'>{$title$}</div><div class='table_close'><a onclick=\"table_Close('" + id + "','" + en + "');\">删除</a></div>" +
            "<table width='100%' class='table_border' border='0' cellpadding='0' cellspacing='0'><tr class='table_head'><td><input id='s_All_" + en.replace(".", "_") +
            "' type='checkbox' onclick=\"select_Table_All('" + id + "','" + en + "');\" value='-1' checked/></td><td>NAME</td><td>CODE</td></tr>{$html$}</table></div>";
        var viewID = $("#hid_ID").val();

        $.ajax({
            type: "POST",
            async: false,  // 设置同步方式 
            url: "/SysTBView/GetTableInfo/?id=" + en + "&viewID=" + viewID,
            success: function (data) {
                if (data != "") {
                    divhtml = divhtml.replace("{$title$}", item[0].name);
                    divhtml = divhtml.replace("{$html$}", data);
                    $(".selectArea").append(divhtml);
                    c_arr[arrNum] = new Array();
                    addToArray($("#div_" + id + " input"), c_arr[arrNum]);

                    var allIndex = c_arr_all.indexOf(en);
                    if (allIndex > -1) {
                        c_arr_all[allIndex] = new Array();
                        addToArray($("#div_" + id + " input"), c_arr_all[allIndex]);
                    }
                    ShowDefinition();
                }
            }
        });
    } else if (!item.is(':checked')) {
        table_Close(id, en);
        ShowDefinition();
    }
}
//--------SELECT-------------------
function select_Table_All(id, en) {
    var selector = $("#div_" + id + " input");
    if ($("#s_All_" + en.replace(".", "_")).is(':checked')) {
        selector.attr("checked", "checked");
    }
    else {
        selector.removeAttr("checked");
    }
    selector.each(function (i, d) {
        var nm = d.className;
        if (jv.IsNull(nm)) return;
        var nmL = nm.toLocaleLowerCase();
        if (nmL == "mdp_guid" || nmL == "mdp_batchtime" || nmL == "mdp_isdelete") {
            $(this).attr("checked", "checked");
            return;
        }
        selectColumn(en, nm);
    });
}
function selectColumn(tb, tc) {
    var rtb = tb.replace(".", "_");
    var col = $("#" + rtb + "_" + tc);
    var index = t_arr.indexOf(tb);
    if (col.is(':checked')) {
        if (index == -1) {
            t_arr.push(tb);
            c_arr.push(tb);
            c_arr[c_arr.length - 1] = new Array();
            c_arr[c_arr.length - 1].push(tb + "." + tc);
        }
        else if (c_arr[index].indexOf(tb + "." + tc) == -1)
            c_arr[index].push(tb + "." + tc);
    } else {
        $("#s_All_" + rtb).removeAttr("checked");
        c_arr[index].removeValue(tb + "." + tc);
        if (c_arr[index].length == 0) {
            t_arr.removeAt(index);
            c_arr.removeAt(index);
        }
    }
    viewAll = false;
    ShowDefinition();
}
function table_Close(id, en) {
    $("#select_All").removeAttr("checked");
    $("#" + id).removeAttr("checked");
    $("#div_" + id).remove();
    var index = t_arr.indexOf(en);
    t_arr.removeAt(index);
    c_arr.removeAt(index);
    if (t_arr.length <= 2)
        UnlockJoin();
    ShowDefinition();
}
//-------------ON-----------------
function deleteTr(i) {
    $("#" + i).remove();
}
function addOn() {
    if (t_arr.length < 2) {
        alert("请选择视图！");
    }
    else {
        var i = $(".onArea tr").length - 1;
        var addHtml = "<tr id='on_" + i + "'><td>" + formatDrop('dropLeftOn_' + i) + "</td><td>=</td><td>" + formatDrop('dropRightOn_' + i) + "</td><td><a onclick=\"deleteTr('on_" + i + "')\">删除</a></td></tr>";

        $(".onArea_end").before(addHtml);
    }
}
function formatDrop(d) {
    var drop_F = "<select class='f" + d + "' onchange=\"DropChange(this.options[this.options.selectedIndex].value,'" + d + "')\">";
    var drop_S = "<select id='" + d + "' class='viewSelect'>";
    var defName;
    for (var i = 0; i < c_arr_all.length; i++) {
        var tbName = c_arr_all[i][0];
        tbName = tbName.substr(0, tbName.lastIndexOf("."));
        if (i == 0)
            defName = tbName;
        drop_F += "<option value='" + i + "' text='" + tbName + "'>" + tbName + "</option>";
    }
    for (var j = 0; j < c_arr_all[0].length; j++) {
        var tcName = c_arr_all[0][j];
        tcName = tcName.replace(defName + ".", "");
        drop_S += "<option value='" + c_arr_all[0][j] + "'>" + tcName + "</option>";
    }
    drop_F += "</select>";
    drop_S += "</select>";
    return drop_F + drop_S;
}
function DropChange(i, d) {
    $("#" + d).empty();
    for (var j = 0; j < c_arr_all[i].length; j++) {
        var tcName = c_arr_all[i][j];
        tcName = tcName.substr(tcName.lastIndexOf(".") + 1);
        $("#" + d).append("<option value='" + c_arr_all[i][j] + "'>" + tcName + "</option>");
    }
}
function DropFormat(n, d) {
    $("#" + d).empty();
    var i = c_arr_all.indexOf(n);
    for (var j = 0; j < c_arr_all[i].length; j++) {
        var tcName = c_arr_all[i][j];
        tcName = tcName.substr(tcName.lastIndexOf(".") + 1);
        $("#" + d).append("<option value='" + c_arr_all[i][j] + "'>" + tcName + "</option>");
    }
}
//---------Where-----------------
function addWhere() {
    if (t_arr.length < 1) {
        alert("请选择视图！");
    }
    else {
        var i = $(".whereArea tr").length - 1;
        var addHtml = "<tr id='where_" + i + "'><td>" + formatDrop('dropViewColumn_' + i) + "</td><td>" + whereAction(i) + "<input type='text' name='whe_ActionText_" + i + "' class='whe_Action' style='width:150px;' /></td><td>" + formatGroup(i) + "</td><td><a onclick=\"deleteTr('where_" + i + "')\">删除</a></td></tr>";

        $(".whereAreaEnd").before(addHtml);
    }
}
function formatGroup(d) {
    var gp = "<select id='grp_GroupBy" + d + "' class='viewSelect'><option value=''>分组依据</option><option value='Min'>Min</option><option value='Max'>Max</option><option value='Count'>Count</option><option value='Where'>Where</option></select>";
    return gp;
}
function whereAction(d) {
    var wha = "<select id='wha_Action" + d + "'><option value='='>=</option><option value='!='>!=</option><option value='>'>></option><option value='<'><</option><option value=' like '>like</option><option value=' not like '>not like</option></select>";
    return wha;
}
//---------Order By-----------------
function addOrderBy() {
    if (t_arr.length < 1) {
        alert("请选择视图！");
    }
    else {
        var i = $(".orderByArea tr").length - 1;
        var addHtml = "<tr id='orderBy_" + i + "'><td>" + formatDrop('dropOrder_' + i) + "</td><td>" + orderAction(i) + "</td><td>" + sortAction(i) + "</td><td><a onclick=\"deleteTr('orderBy_" + i + "')\">删除</a></td></tr>";

        $(".orderByAreaEnd").before(addHtml);
    }
}
function orderAction(d) {
    var od = "<select id='oda_SortType" + d + "'><option value='asc'>升序</option><option value='desc'>降序</option></select>";
    return od;
}
function sortAction(d) {
    var sort = "<select id='sor_SortNum" + d + "'>";
    for (var i = 1; i <= d; i++) {
        sort += "<option value='" + i + "'>" + i + "</option>";
    }
    sort += "</select>";
    return sort;
}
//---------Definition------------ 
function ShowDefinition(useOn) {
    if (c_arr.length > 0 && c_arr[0].length > 0) {
        var definition = "";
        if (useOn) {
            //--------Check Where------------
            var grp = false; //是否分组
            var defGroup = "";
            var defSelect = new Array();
            for (var ci = 0; ci < c_arr.length; ci++) {
                defSelect.push("");
                var fi = defSelect.length - 1;
                defSelect[fi] = new Array();
                for (var cii = 0; cii < c_arr[ci].length; cii++) {
                    defSelect[fi].push(c_arr[ci][cii]);
                }
            }
            var defWhere = new Array();
            var defHaving = new Array();
            for (var i = 0; i < where_Arr.length; i++) {
                if (where_Arr[i][3] != "") {
                    grp = true;
                }
            }
            if (grp) {
                defGroup = " GROUP BY " + defSelect.join(",");
            }
            for (var i = 0; i < where_Arr.length; i++) {
                if (grp) {
                    if (where_Arr[i][3] == "Where")
                        defWhere.push(where_Arr[i][0] + where_Arr[i][1] + where_Arr[i][2]);
                    else if (where_Arr[i][3] != "") {
                        if (defGroup.indexOf(where_Arr[i][0]) == -1)
                            defGroup += "," + where_Arr[i][0];
                        var exf = where_Arr[i][3] + "(" + where_Arr[i][0] + ")";
                        var exfc = where_Arr[i][0].substr(where_Arr[i][0].lastIndexOf(".") + 1);
                        defHaving.push(exf + where_Arr[i][1] + where_Arr[i][2]);
                        for (var j = 0; j < defSelect.length; j++) {
                            for (var k = 0; k < defSelect[j].length; k++) {
                                if (defSelect[j][k] == where_Arr[i][0]) {
                                    defSelect[j][k] = exf + " as " + exfc + j + k;
                                }
                            }
                        }
                    }
                    else if (where_Arr[i][3] == "") {
                        if (defGroup.indexOf(where_Arr[i][0]) == -1)
                            defGroup += "," + where_Arr[i][0];
                        defHaving.push(where_Arr[i][0] + where_Arr[i][1] + where_Arr[i][2]);
                    }
                }
                else {
                    defWhere.push(where_Arr[i][0] + where_Arr[i][1] + where_Arr[i][2]);
                }
            }
            //--------Definition Sql------------
            var ship = " " + $('#viewShip input:radio:checked').val() + " "; //Join sql
            definition = "SELECT ";
            if (order_Arr.length > 0)
                definition += " TOP 100 percent ";

            var expr = new Array();
            var renameCount = 1;
            for (var selIndex = 0; selIndex < defSelect.length; selIndex++) {
                for (var exp = 0; exp < defSelect[selIndex].length; exp++) {
                    var cc = defSelect[selIndex][exp].substr(defSelect[selIndex][exp].lastIndexOf("."));
                    if (ArrIndexOf(defSelect, cc)) {
                        if (ArrIndexOf(expr, cc)) {
                            defSelect[selIndex][exp] = defSelect[selIndex][exp] + " as Expr" + renameCount;
                            renameCount += 1;
                        }
                        else
                            expr.push(cc);
                    }
                }
            }

            definition += defSelect.join(",");

            //--On sql
            definition += " FROM " + t_arr[0];
            var onErr = false;
            var viewShip = " " + $('input[name="rdb_ViewAction"]:checked').val() + " ";
            for (var i = 1; i < t_arr.length; i++) {
                onErr = true;
                definition += viewShip + t_arr[i] + " ON ";
                for (var j = 0; j < on_Arr.length; j++) {
                    if (CheckArray(t_arr, t_arr.length, on_Arr[j][0].substr(0, on_Arr[j][0].lastIndexOf("."))) && CheckArray(t_arr, t_arr.length, on_Arr[j][1].substr(0, on_Arr[j][1].lastIndexOf(".")))) {
                        {
                            if (j > 0)
                                definition += " AND ";
                            definition += on_Arr[j][0] + "=" + on_Arr[j][1];
                            on_Arr.removeAt(j);
                            onErr = false;
                        }
                    }
                }
            }
            if (onErr) {
                alert("表关联错误，请检查关联设置！");
                return;
            }

            //--order sql
            var defOrder = "";
            for (var i = 0; i < order_Arr.length; i++) {
                if (i == 0)
                    defOrder += " ORDER BY ";
                else
                    defOrder += ",";
                if (defGroup.indexOf(order_Arr[i][0]) == -1 && grp)
                    defGroup += "," + order_Arr[i][0];
                defOrder += order_Arr[i][0] + " " + order_Arr[i][1];
            }
            //--Where sql
            if (defWhere.length > 0)
                definition += " WHERE " + defWhere.join(" AND ");

            //--GroupBy sql
            definition += defGroup;

            //--Having sql
            if (defHaving.length > 0)
                definition += " HAVING " + defHaving.join(" AND ");

            definition += defOrder;

        } else {
            definition = "SELECT " + c_arr.join(",") + " FROM " + t_arr.join(",");
        }
        $("#txt_Definition").val(definition);
    }
    else
        $("#txt_Definition").val("");
    $("#div_Definition").html($("#txt_Definition").val());

    if (t_arr.length > 1) {
        if ($('#viewShip input:radio:checked').length == 0)
            $("#viewShip input[type=radio]")[0].checked = true;
    } else {
        $(".onArea tr:not(.dmo)").remove();
    }
    if (t_arr.length < 1) {
        $(".whereArea tr:not(.dmo)").remove();
        $(".orderByArea tr:not(.dmo)").remove();
    }
}

function CreateDefinition() {
    //---ON---

    if (t_arr.length > 1 && $(".onArea tr:not(.dmo)").length < 1) {
        alert("请添加表关联关系ON！");
        return;
    }
    on_Arr.splice(0, on_Arr.length);
    $(".onArea tr:not(.dmo)").each(function (d) {
        var i = on_Arr.push("") - 1;
        on_Arr[i] = new Array();
        addToArray($(this).find(".viewSelect"), on_Arr[i]);
    });
    for (var i = 0; i < on_Arr.length; i++) {
        if (on_Arr[i][0] == on_Arr[i][1]) {
            alert("表字段不能自己关联自己，请检查表关联关系ON！");
            return;
        }
    }
    //----WHERE -------

    var err = false;
    $(".whereArea tr:not(.dmo) input").each(function () {
        if ($.trim($(this).val()) == "") {
            err = true;
            return;
        }
    });
    if (err) {
        alert("WHERE筛选器不能为空！");
        return;
    }
    where_Arr.splice(0, where_Arr.length);
    $(".whereArea tr:not(.dmo)").each(function (d) {
        var i = where_Arr.push("") - 1;
        where_Arr[i] = new Array();
        addToArray($(this).find("select,:text"), where_Arr[i]);
    });
    //--------Order By-----------
    order_Arr.splice(0, order_Arr.length);
    $(".orderByArea tr:not(.dmo)").each(function (d) {
        var i = order_Arr.push("") - 1;
        order_Arr[i] = new Array();
        addToArray($(this).find("select"), order_Arr[i]);
    });
    ShowDefinition(true);
}
function CheckArray(arr, len, v) {
    for (var i = 0; i <= len; i++) {
        if (arr[i] == v)
            return true;
    }
    return false;
}
function addToArray(obj, arr) {
    for (var i = 0; i < obj.length; i++) {
        var push = true;
        var d = obj[i];
        var nm = d.name || d.id;
        if (jv.IsNull(nm)) push = false;
        if (typeof (d.value) == "undefined" || d.value == "-1") push = false;
        if (push) {
            arr.push(d.value);
        }
    }
} 